Option Explicit
Option Strict

Imports System
Imports System.Data
Imports System.Data.SqlClient


namespace BusObjectVB 

   public class IBAProducts 

      private  m_DSN as String

      public Sub New ()
      	MyBase.New
      	m_DSN = ""
      end sub

		public Sub New(DSN as string) 
			MyBase.New
			m_DSN = DSN
		end sub

		public Property DatabaseConnection as string 
			set 
				m_DSN = value
			end set
			get
				return m_DSN
			end get
		end Property


      public function  GetProducts ( productType as String)  as DataSet
      	  if m_DSN = "" then
      	  	throw(new ArgumentNullException("DatabaseConnection", "No value for the database connection string"))
      	  end if
          dim myConnection as new SqlConnection(m_DSN)
          dim sqlAdapter1 as new SqlDataAdapter("SELECT * FROM Products WHERE ProductType='"+productType+"'", myConnection)

          dim  products as new DataSet()
          sqlAdapter1.Fill(products, "products")

          return products
      end function

      public function GetProductTypes () as DataSet

      	  if m_DSN = "" then
      	  	throw(new ArgumentNullException("DatabaseConnection", "No value for the database connection string"))
      	  end if

          dim myConnection as new SqlConnection(m_DSN)

         dim sqlAdapter1 as new SqlDataAdapter("SELECT DISTINCT ProductType FROM Products", myConnection)

         dim types as new DataSet()
         sqlAdapter1.Fill(types, "ProdTypes")
			
         return types
      end function

      public function AveragePrice ( productType as string) as Double

      	  if m_DSN = "" then
      	  	throw(new ArgumentNullException("DatabaseConnection", "No value for the database connection string"))
      	  end if
          dim myConnection as new SqlConnection(m_DSN)

         dim sqlAdapter1 as new SqlDataAdapter("SELECT AVG(UnitPrice) AS AveragePrice FROM Products WHERE ProductType='"+productType+"'", myConnection)

         dim AvgPrice as new DataSet()
         sqlAdapter1.Fill(AvgPrice, "AveragePrice")


         dim priceTable as DataTable
         priceTable = AvgPrice.Tables("AveragePrice")
         if (not priceTable.Rows(0).IsNull("AveragePrice"))
            return CDbl(priceTable.Rows(0)("AveragePrice"))
         else
            return 0
         end if
      end function
   end class
end namespace
